﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Web;
using System.Web.Script.Serialization;
using System.IO;
using System.Data.SqlClient;
using MarchEstate.Manager.BusinesEntity;



public class LoadExeclController : AbstractJqueryController
{
   
    /// <summary>
    /// 上传文件
    /// </summary>
    public void UploadFile()
    {
        try
        {
            HttpPostedFile file = context.Request.Files["Filedata"];

            UploadDocumentItem objUploadDocumentItem = new UploadDocumentItem();
            string uploadPath = HttpContext.Current.Server.MapPath(@"/jquery-easyui/uploadify/UploadFiles/") + "\\";
            objUploadDocumentItem.DocName = file.FileName;
            objUploadDocumentItem.UploadDate = DateTime.Now.ToShortDateString();
            objUploadDocumentItem.DocPath = uploadPath + objUploadDocumentItem.DocName;
            objUploadDocumentItem.DocLength = file.ContentLength;


            if (file != null)
            {
                //如果没有该目录则创建该上传目录
                if (!Directory.Exists(uploadPath))
                {
                    Directory.CreateDirectory(uploadPath);
                }
                file.SaveAs(uploadPath + objUploadDocumentItem.DocName);
                context.Response.Write(1);
            }
            else
            {
                context.Response.Write("0");
            }
        }
        catch (Exception err)
        {
            throw err;
        }
    }
    
    /// <summary>
    /// 加载文件数据
    /// </summary>
    public void loadFileData()
    {
        try
        {
           
            string filePath = HttpContext.Current.Server.MapPath(@"/jquery-easyui/uploadify/UploadFiles/");
            DirectoryInfo filedir = new DirectoryInfo(filePath);
           
            List<UploadDocumentItem> objUploadDocumentItems = new List<UploadDocumentItem>();
           //加载所有文件
            foreach (FileInfo fi in filedir.GetFiles())
            {
                UploadDocumentItem objUploadDocumentItem = new UploadDocumentItem();
                if (fi.Extension.ToLower().Trim() == ".xls" || fi.Extension.ToLower().Trim() == ".xlsx")
                {
                    objUploadDocumentItem.DocName = fi.Name;
                    objUploadDocumentItem.UploadDate = fi.LastWriteTime.ToShortDateString();
                    objUploadDocumentItem.DocLength = fi.Length;
                    objUploadDocumentItem.DocPath = fi.FullName;
                }
                objUploadDocumentItems.Add(objUploadDocumentItem);
            }
            TxtJson = base.ListToJson(objUploadDocumentItems);
        }
        catch (Exception err)
        {
            throw err;
        }
    }
    
    /// <summary>
    /// 删除文件
    /// </summary>
    public void DeleteDocument()
    {
        try
        {
            //获得文件完整路径
            string fullFileName = ParamsData["docpath"].ToString();
            System.IO.FileInfo file = new System.IO.FileInfo(fullFileName);
            if (file.Exists)
            {
                file.Delete();//删除文件
            }
            if (!string.IsNullOrEmpty(fullFileName))
            {
                TxtJson = ReturnSuccess("删除成功！");
            }
        }
        catch (Exception err)
        {
            throw err;
        }
    }

    /// <summary>
    /// 导入到数据库
    /// </summary>
    public void ExcelToSql()
    {
        try
        {
            //获得文件完整路径
            string fullFileName = ParamsData["docpath"].ToString();
            System.IO.FileInfo file = new System.IO.FileInfo(fullFileName);
            DataSet ds = new DataSet();
            DataRow[] dr=null;
            if (file.Exists)
            {
                ds = ExecleDs(fullFileName, file.Name);  //从Excel读取数据
                dr = ds.Tables[0].Select();            //获取DataRow数组
            }
            int rowsnum = ds.Tables[0].Rows.Count;
            if (rowsnum == 0)
            {
                TxtJson = ReturnError("Excel表为空表,无数据!");   //当Excel表为空时,对用户进行提示
            }
            else
            {
                AbstractOwner owner = NewObject<AbstractOwner>();
                List<AbstractOwner> ownerList =new List<AbstractOwner>();
                for (int i = 0; i < dr.Length; i++)
                {
                    owner.Name = dr[i]["客户姓名"].ToString();// excel列名【名称不能变,否则就会出错】
                    owner.Sex = string.IsNullOrEmpty(dr[i]["性别"].ToString()) ? 0 : Convert.ToInt32(dr[i]["性别"].ToString());//性别 列名 以下类似
                    owner.BirthdayDate = Convert.ToDateTime(string.IsNullOrEmpty(dr[i]["出生日期"].ToString()) ? "2011-01-01" : (dr[i]["出生日期"].ToString()));
                    owner.CardNum = dr[i]["身份证号码"].ToString();
                    owner.OftenMobile = dr[i]["手机号"].ToString();
                    owner.QQ = dr[i]["QQ"].ToString();
                    owner.Mail = dr[i]["邮箱"].ToString();
                    owner.OftenRelaAdress = dr[i]["联系地址"].ToString();
                    owner.RoomNumber = dr[i]["房号"].ToString();
                    ownerList.Add(owner);
                }
                owner.Save(ownerList);
                TxtJson = ReturnSuccess("Excel表导入成功!");
            }
        }
        catch (Exception err)
        {
            TxtJson = ReturnError(err.Message.ToString());
        }
    }

    public DataSet ExecleDs(string filenameurl, string table)
    {
        string strConn = "Provider=Microsoft.Jet.OleDb.4.0;" + "data source=" + filenameurl + ";Extended Properties='Excel 8.0; HDR=YES; IMEX=1'";
        OleDbConnection conn = new OleDbConnection(strConn);
        conn.Open();
        DataSet ds = new DataSet();
        OleDbDataAdapter odda = new OleDbDataAdapter("select * from [Sheet$]", conn);
        odda.Fill(ds, table);
        return ds;
    }

    //文件信息对象
    public class UploadDocumentItem
    {
        public UploadDocumentItem()
        { }
        public string DocName { get; set; }
        public string DocPath { get; set; }
        public long DocLength { get; set; }
        public string UploadDate { get; set; }
    }

}

